MySQL数据库运维之读写分离03

上篇文章详细介绍了MySQL中间件MyCat的搭建及读写分离操作。在实际场景中，面对大容量的数据，在数据库层面中通常的解决方案有两种，第一种是读写分离，第二种就是数据库的分库和分表了，本篇文章将基于MyCat中间件，详细介绍一下通过MyCat综合实现分库、分表及读写分离操作！微信搜索公众号：”菜鸟封神记“，定期分享一线大厂常用技术干货。

一、基础回顾

上两篇文章中已经介绍了Mycat的基本使用，但MyCat的核心工作原理有必要此处再强调一下，利于理解后面其他内容，如下：

客户端连接MyCat代理，并发送MySQL的操作语句
MyCat拦截用户发送的SQL语句
拦截到SQL语句之后，会通过内部的一系列解析器完成解析，并做一些特定的分析，比如：分片分析、路由分析、读写分离分析、缓存分析等
分析完成之后，根据分析出的结果，比如SQL操作需要路由到哪个数据库实例，哪个数据库，哪个表。然后将SQL语句发送到需要路由到的库或者表
数据库实例执行完SQL操作之后，将结果返回到MyCat代理，MyCat代理对结果做一些适当的处理，然后再将结果返回给发送SQL语句的客户端。至此，一次数据库操作完成！

二、环境准备

1、数据库软件

MySQL5.6.39

2、MyCat中间件

Mycat-server-1.6

3、服务器环境

CenterOS6.9虚拟机五台

4、服务器划分

192.168.0.4（第一台主库mysql-server01）
192.168.0.5（第一台从库mysql-server02）
192.168.0.6（MyCat代理服务器mysql-proxy）
192.168.0.7（第二台主库mysql-server03）
192.168.0.8（第二台从库mysql-server04）

三、分库分表及读写分离配置

1、安装MySQL数据库，并配置主从复制，安装及配置主从同步请参考之前的文章，有详细讲解，此处不再赘述

mysql-server02同步mysql-server01
mysql-server04同步mysql-server03

2、配置mysql-proxy，进入到MyCat的安装目录，然后修改server.xml

#备份原文件
[root@mysql-proxy ~]# cd /usr/local/mycat/conf/
[root@mysql-proxy conf]# cp server.xml server.xml.bak

添加如下两个用户，一个为只读用户，一个为读写用户：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">test_db</property>
                <!--
                <privileges check="false">
                        <schema name="test_db" dml="0110">
                                <table name="student" dml="0000"></table>
                                <table name="t_user" dml="1111"></table>
                        </schema>
                </privileges>
                -->
        </user>
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">test_db</property>
                <property name="readOnly">false</property>
        </user>
</mycat:server>

3、配置schema.xml配置文件

#备份原文件
[root@mysql-proxy conf]# cp schema.xml schema.xml.bak
[root@mysql-proxy conf]# vim schema.xml

写入如下内容：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100">
                <table name="t_user" primaryKey="id" dataNode="wb_node01,wb_node02" rule="sharding-by-mod" />
        </schema>
        <dataNode name="wb_node01" dataHost="wb_host01" database="test_db" />
        <dataNode name="wb_node02" dataHost="wb_host02" database="test_db" />
        <dataHost name="wb_host01" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="mysql-server01" url="192.168.0.4:3306" user="root" password="root">
                        <readHost host="mysql-server02" url="192.168.0.5:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="mysql-server02" url="192.168.0.5:3306" user="root" password="root" />
        </dataHost>
        <dataHost name="wb_host02" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="mysql-server03" url="192.168.0.7:3306" user="root" password="root">
                        <readHost host="mysql-server04" url="192.168.0.8:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="mysql-server04" url="192.168.0.8:3306" user="root" password="root" />
        </dataHost>
</mycat:schema>

部分内容解释：

dataNode节点表示实际的物理节点，此处使用了两组主从，dataNode中的节点分表表示两台从服务器
schema节点中的table表示需要拆分的表，rule表示分表策略的名称，与rule.xml中的tableRule节点中的name属性对应

4、修改rule.xml，配置分表策略

[root@mysql-proxy conf]# cp rule.xml rule.xml.bak
[root@mysql-proxy conf]# vim rule.xml

配置分表策略，如下是按照主键ID取模进行分表：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="sharding-by-mod">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- 表示数据节点的数量 -->
                <property name="count">2</property>
        </function>
</mycat:rule>

5、启动mycat服务，并插入两条数据

[root@mysql-proxy conf]# cd /usr/local/mycat/bin/
[root@mysql-proxy bin]# ./mycat start

使用user用户连接mycat，然后插入两条数据，如下：

#连接mycat服务
[root@mysql-proxy bin]# mysql -uuser -puser -P8066 -h127.0.0.1

#查看数据库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test_db  |
+----------+

#在t_user表中插入两条记录
mysql> use test_db;
mysql> insert into t_user(id,name,age,address) values(1,"wangbin",24,"Shan'Xi");
mysql> insert into t_user(id,name,age,address) values(2,"binwang",21,"Bei'Jing");

注意：上述插入数据时需要自己提供id，可以使用序列的方式或者ID生成器自己生成，此处暂时不介绍，如果不提供ID，会插入失败。
6、分别登陆mysql-server01和mysql-server03查看插入的数据
登陆mysql-server01，查看如下：

[root@mysql-server01 ~]# mysql -uroot -proot -P3306 -h127.0.0.1 test_db
#查数据
root@127.0.0.1 test_db> select * from t_user;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  2 | binwang |   21 | BeiJing |
+----+----------+------+---------+
1 row in set (0.00 sec)

登陆mysql-server03，查看如下：

[root@mysql-server03 ~]# mysql -uroot -proot -h127.0.0.1 -P3306 test_db
#查数据
root@127.0.0.1 test_db> select * from t_user;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  1 | wangbin |   24 | Shan'Xi |
+----+----------+------+---------+
1 row in set (0.00 sec)

从上述结果看到，id=1的数据和id=2的数据分别被分到mysql-server01组合mysql-server03组中。至此，基于MyCat的分库分表已经配置完成，读写分离可以通过监控MyCat的实时日志验证，日志位置为：/usr/local/mycat/logs/wrapper.log，可以自行查看，之前文章介绍过，此处不再赘述。
7、查看节点当前状态

[root@mysql-proxy bin]# mysql -uuser -puser -P9066 -h127.0.0.1
mysql> show @@datanode;
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME      | DATHOST           | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| wb_node01 | wb_host01/test_db |     0 | mysql |      0 |   10 | 1000 |     140 |          0 |        0 |       0 |            -1 |
| wb_node02 | wb_host02/test_db |     0 | mysql |      0 |   10 | 1000 |     139 |          0 |        0 |       0 |            -1 |
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

可以看到，节点目前都处于存活状态。

以上内容介绍了使用ID取模的方式借助MyCat中间件实现数据库的分库分表及读写分离过程。文章中概念内容介绍的比较少，因为在MyCat的官网上有更详细的概念描述。如有不明白，可下方评论。欢迎转发！

后续更多文章将更新在个人小站上，欢迎查看。

另外提供一些优秀的IT视频资料，可免费下载！如需要请查看 https://www.592xuexi.com

MySQL数据库运维之读写分离03

一、基础回顾

二、环境准备

三、分库分表及读写分离配置

夏日寒冰

引用和评论

面试常问的Spring源码，出maven版本了

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

Mysql梳理1

mysql梳理2